package edu.hhu.basic.table;

import java.io.FileOutputStream;
import java.io.IOException;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;

import com.jfinal.plugin.activerecord.Db;
import com.jfinal.plugin.activerecord.Page;
import com.jfinal.plugin.activerecord.Record;

import edu.hhu.dynamic.table.TableDao;

public class CreateExcel {
	public void writeExcel(String filename, String table,String[] split,String check) throws IOException {
		// 创建新的Excel工作薄
		HSSFWorkbook workbook = new HSSFWorkbook();
		//SXSSFWorkbook workbook = new SXSSFWorkbook(1000);
		// 在Excel中新建一张工作表，起名为jsp
		HSSFSheet sheet = workbook.createSheet("数据导出1");
        //Sheet sheet = workbook.createSheet("数据导出1");
		BTableDao tableDao = new BTableDao();
		// 表ge 标题处理
		head(sheet, tableDao,table);
		String sql=null;
		List<Record> data=null;
		if("2".equals(check)){
			 sql = "select * from"+" "+table+" "+"where ID in(";
			for(int i=0;i<split.length;i++){
				sql += "?";
				if (i < split.length - 1) {
					sql += ",";
				}
			}
			sql += ")";
			System.out.println(sql);
			data = Db.use("mysql").find(sql,split);
		}else if("1".equals(check)){
			 sql = "select * from"+" "+table ;
			 data = Db.use("mysql").find(sql);
		}else if("3".equals(check)){
			//String[] split = para.split(",");
			if(split.length==11&&"".equals(split[7])){
				   sql = "select c.wellcode,c.wellname,c.elevation,c.depth,c.totalthickness,c.netthickness,c.stratumpressure,c.SL,c.PLYL,c.PJSB,d.SCRQ,d.QQ,d.QL  from (select a.wellcode,a.wellname,a.elevation,a.depth,a.totalthickness,a.netthickness,a.stratumpressure,b.SL,b.PLYL,b.PJSB from jtjcsj a inner join ylsgsj b on a."+split[1]+split[2]+"? and a.wellcode=b.wellcode ) c inner join pcdtsjtrue d on c.wellcode=d.wellcode";
				   System.out.println(sql);
				   data = Db.use("mysql").find(sql,split[3]);
			}else if(split.length==11&&"".equals(split[3])){
			       sql = "select c.wellcode,c.wellname,c.elevation,c.depth,c.totalthickness,c.netthickness,c.stratumpressure,c.SL,c.PLYL,c.PJSB,d.SCRQ,d.QQ,d.QL  from (select a.wellcode,a.wellname,a.elevation,a.depth,a.totalthickness,a.netthickness,a.stratumpressure,b.SL,b.PLYL,b.PJSB from jtjcsj a inner join ylsgsj b on b."+split[5]+split[6]+"? and a.wellcode=b.wellcode ) c inner join pcdtsjtrue d on c.wellcode=d.wellcode";
				   System.out.println(sql);
				   data = Db.use("mysql").find(sql,split[7]);
			}else if(split.length==12&&"".equals(split[3])&&"".equals(split[7])){
			       sql = "from (select a.wellcode,a.wellname,a.elevation,a.depth,a.totalthickness,a.netthickness,a.stratumpressure,b.SL,b.PLYL,b.PJSB from jtjcsj a inner join ylsgsj b on a.wellcode=b.wellcode ) c inner join pcdtsjtrue d on d."+split[9]+split[10]+"?  and c.wellcode=d.wellcode";
				    System.out.println(sql);
				   data = Db.use("mysql").find(sql,split[11]);
			}else if(split.length==11&&!("".equals(split[3]))&&!("".equals(split[7]))){
			       sql = "select c.wellcode,c.wellname,c.elevation,c.depth,c.totalthickness,c.netthickness,c.stratumpressure,c.SL,c.PLYL,c.PJSB,d.SCRQ,d.QQ,d.QL from (select a.wellcode,a.wellname,a.elevation,a.depth,a.totalthickness,a.netthickness,a.stratumpressure,b.SL,b.PLYL,b.PJSB from jtjcsj a inner join ylsgsj b on a."+split[1]+split[2]+"? and b."+split[5]+split[6]+"? and a.wellcode=b.wellcode ) c inner join pcdtsjtrue d on c.wellcode=d.wellcode";
				    System.out.println(sql);
				    data = Db.use("mysql").find(sql,split[3],split[7]);

			}else if(split.length==12&&!("".equals(split[3]))&&("".equals(split[7]))){
			       sql = "select c.wellcode,c.wellname,c.elevation,c.depth,c.totalthickness,c.netthickness,c.stratumpressure,c.SL,c.PLYL,c.PJSB,d.SCRQ,d.QQ,d.QL from (select a.wellcode,a.wellname,a.elevation,a.depth,a.totalthickness,a.netthickness,a.stratumpressure,b.SL,b.PLYL,b.PJSB from jtjcsj a inner join ylsgsj b on a."+split[1]+split[2]+"? and a.wellcode=b.wellcode ) c inner join pcdtsjtrue d on d."+split[9]+split[10]+"?  and c.wellcode=d.wellcode";
				   System.out.println(sql);
				   data = Db.use("mysql").find(sql,split[3],split[11]);

			}else if(split.length==12&&("".equals(split[3]))&&!("".equals(split[7]))){
			       sql = "select c.wellcode,c.wellname,c.elevation,c.depth,c.totalthickness,c.netthickness,c.stratumpressure,c.SL,c.PLYL,c.PJSB,d.SCRQ,d.QQ,d.QL from (select a.wellcode,a.wellname,a.elevation,a.depth,a.totalthickness,a.netthickness,a.stratumpressure,b.SL,b.PLYL,b.PJSB from jtjcsj a inner join ylsgsj b on b."+split[5]+split[6]+"? and a.wellcode=b.wellcode ) c inner join pcdtsjtrue d on d."+split[9]+split[10]+"?  and c.wellcode=d.wellcode";
				   System.out.println(sql);
				   data = Db.use("mysql").find(sql,split[7],split[11]);

			}else if(split.length==12&&!("".equals(split[3]))&&!("".equals(split[7]))){
		       sql = "select c.wellcode,c.wellname,c.elevation,c.depth,c.totalthickness,c.netthickness,c.stratumpressure,c.SL,c.PLYL,c.PJSB,d.SCRQ,d.QQ,d.QL from (select a.wellcode,a.wellname,a.elevation,a.depth,a.totalthickness,a.netthickness,a.stratumpressure,b.SL,b.PLYL,b.PJSB from jtjcsj a inner join ylsgsj b on a."+split[1]+split[2]+"? and b."+split[5]+split[6]+"? and a.wellcode=b.wellcode ) c inner join pcdtsjtrue d on d."+split[9]+split[10]+"?  and c.wellcode=d.wellcode";
		      System.out.println(sql);
		      data = Db.use("mysql").find(sql,split[3],split[7],split[11]);
			}
			
		      /*sql = "select c.wellcode,c.wellname,c.elevation,c.depth,c.totalthickness,c.netthickness,c.stratumpressure,c.SL,c.PLYL,c.PJSB,d.SCRQ,d.QQ,d.QL  from (select a.wellcode,a.wellname,a.elevation,a.depth,a.totalthickness,a.netthickness,a.stratumpressure,b.SL,b.PLYL,b.PJSB from jtjcsj a inner join ylsgsj b on a."+split[1]+split[2]+"? and b."+split[5]+split[6]+"? and a.wellcode=b.wellcode ) c inner join pcdtsjtrue d on d."+split[9]+split[10]+"?  and c.wellcode=d.wellcode";
		      System.out.println(sql);
		      data = Db.use("mysql").find(sql,split[3],split[7],split[11]);*/
			
		}
		// 表数据
		
		// System.out.println(sql);
		List<Record> fields = tableDao.getFields(table);
		// System.out.println(data);
		//String[] fields = fieldStr.split(",");
		// System.out.println(fields.length);
		int i = 1;
		int num = 1;
		for (Record record : data) {
			Map temp = record.getColumns();
			// 创建第一行
			HSSFRow row = sheet.createRow(i);
			for (int j = 0; j < fields.size(); j++) {
				HSSFCell cell0 = row.createCell(j);
				cell0.setCellType(HSSFCell.CELL_TYPE_STRING);
				String s =fields.get(j).getStr("fieldcode");
				Object obj=temp.get(s);
				if(obj==null){
				  obj="";
				}
				String str= obj.toString();
				if(i%10000==0){
					System.out.println(s+":"+str);
				}
				//System.out.println(s+":"+str);
				cell0.setCellValue(str);
			}
			i++;
			
			if (i % 65530 == 0) {
				num++;
				sheet = workbook.createSheet("数据导出" + num);
				i = 0;
			}
		}

		// 新建输出流
		FileOutputStream fout = new FileOutputStream(filename);
		// 存盘
		workbook.write(fout);

		fout.flush();
		// 结束关闭
		fout.close();
		// System.out.println("sq123");

	}

	public void head(HSSFSheet sheet, BTableDao tableDao,String table) {
		// 表标题
		List<Map> heads = tableDao.getHaeder(table);
		//Map head = new HashMap();
		/*for (Map map : heads) {
			head.put(map.get("fieldcode"), map.get("fieldname"));
		}*/
		List<Record> fields = tableDao.getFields(table);

		// 创建第一行
		HSSFRow row0 = sheet.createRow(0);
		for (int i = 0; i < heads.size(); i++) {
			// 创建第一列
			HSSFCell cell = row0.createCell(i );
			// 定义单元格为字符串类型
			cell.setCellType(HSSFCell.CELL_TYPE_STRING);
			// 在单元格输入一些 内容
			String s = fields.get(i).getStr("fieldcode");
			String str=heads.get(i).get("fieldname").toString();
			cell.setCellValue(str);
		}

	}

	public void writeExcel(String filename) throws IOException {
		// 创建新的Excel工作薄
		HSSFWorkbook workbook = new HSSFWorkbook();
		// 在Excel中新建一张工作表，起名为jsp
		HSSFSheet sheet = workbook.createSheet("Jsp");
		// 创建第一行
		HSSFRow row = sheet.createRow(0);
		// 创建第一列
		HSSFCell cell = row.createCell(0);
		// 定义单元格为字符串类型
		cell.setCellType(HSSFCell.CELL_TYPE_STRING);
		// 在单元格输入一些 内容
		cell.setCellValue(new Date());
		// 新建输出流
		FileOutputStream fout = new FileOutputStream(filename);
		// 存盘
		workbook.write(fout);

		fout.flush();
		// 结束关闭
		fout.close();
	}

}
